package com.atguigu.gmall.mapper;

import com.atguigu.gmall.beans.ProductStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * @Description: 商品主题统计mapper接口, 使用包扫描, 自动加载并执行 @Select注解标识的SQL
 * @Author: tiancy
 * @Create: 2021/11/26
 */
public interface ProductStatsMapper {
    //计算某一天的总交易额
    @Select("select sum(order_amount) order_amount from product_stats_0609 where toYYYYMMDD(stt)=#{date}")
    BigDecimal selectGMV(Integer date);


    /**
     * TODO 统计商品主题指标 : 品牌、品类、商品spu相关指标接口.
     *
     * @Select 注解, 用于帮助我们查询指定的SQL并将结果进行封装.
     * @Param 注解, 这里是`MyBatis`中定义的注解,用于声明当前传递的参数名称.
     */

    /*
        某品牌每天商品的销售额.
        @Select 注解,用于帮助我们查询指定的SQL并将结果进行封装.
        查询结果,`MyBatis`会根据我们查询的字段名称和实体类进行相关关联.
        tm_id,tm_name,order_amount
        2,苹果,889882.00
        5,小米,527957.64
        3,华为,398094.36
        4,TCL,290166.00
        1,Redmi,117399.00
     */
    @Select("select tm_id, tm_name, sum(order_amount) order_amount " +
            "from product_stats_0609 " +
            "where toYYYYMMDD(stt) = #{date} " + // SQL中的`data`和`limit`需要我们前端作为参数传递过来,不能写死,这里使用mybatis中的占位符来占位
            "group by tm_id, tm_name " +
            "having order_amount > 0 " +
            "order by order_amount desc " +
            "limit #{limit}")
    List<ProductStats> selectProductStatsByTm(@Param("date") Integer date, @Param("limit") Integer limit);

    //获取某一天类别交易额
    @Select("select " +
            " category3_id,category3_name,sum(order_amount) order_amount " +
            " from product_stats_0609 where  toYYYYMMDD(stt) = #{date} " +
            " group by  category3_id,category3_name having  order_amount > 0 order by  order_amount desc  limit #{limit}")
    List<ProductStats> selectProductStatsByCategory3(@Param("date") Integer date, @Param("limit") Integer limit);

    //获取某一天SPU交易额排名
    @Select("select spu_id,spu_name,sum(order_amount) order_amount,sum(order_ct) order_ct " +
            " from product_stats_0609 where  toYYYYMMDD(stt) = #{date} group by spu_id,spu_name having order_amount > 0 order by  order_amount desc")
    List<ProductStats> selectProductStatsBySpu(Integer date);


}